CREATE TABLE FileGroupAnalysisJob
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    FileGroupID INT NOT NULL REFERENCES FileGroup(ID),
    TaskQueuedTime DATETIME2 NOT NULL,
    TaskPriority INT NOT NULL,
    ProcessingStartTime DATETIME2 NOT NULL,
    ProcessingEndTime DATETIME2 NOT NULL,
    ProcessingVersion INT NOT NULL
)
GO

CREATE NONCLUSTERED INDEX IX_FileGroupAnalysisJob_FileGroupID
ON FileGroupAnalysisJob(FileGroupID ASC)
GO

CREATE NONCLUSTERED INDEX IX_FileGroupAnalysisJob_TaskQueuedTime
ON FileGroupAnalysisJob(TaskQueuedTime ASC)
GO

CREATE NONCLUSTERED INDEX IX_FileGroupAnalysisJob_ProcessingStartTime
ON FileGroupAnalysisJob(ProcessingStartTime ASC)
GO

CREATE NONCLUSTERED INDEX IX_FileGroupAnalysisJob_ProcessingEndTime
ON FileGroupAnalysisJob(ProcessingEndTime ASC)
GO

DROP TABLE DataOperationFailure
GO

CREATE TABLE DataOperationFailure
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    DataOperationID INT NOT NULL REFERENCES DataOperation(ID),
    FileGroupAnalysisJobID INT NOT NULL REFERENCES FileGroupAnalysisJob(ID),
    [Log] VARCHAR(1000) NOT NULL,
    StackTrace VARCHAR(MAX) NOT NULL,
    TimeOfFailure DATETIME NOT NULL
)
GO

CREATE NONCLUSTERED INDEX IX_DataOperationFailure_DataOperationID
ON DataOperationFailure(DataOperationID ASC)
GO

CREATE NONCLUSTERED INDEX IX_DataOperationFailure_FileGroupAnalysisJobID
ON DataOperationFailure(FileGroupAnalysisJobID ASC)
GO

INSERT INTO NodeType VALUES('WatchDirectoryBalancer', 'openXDA.Nodes.dll', 'openXDA.Nodes.Types.WatchDirectoryBalancing.WatchDirectoryBalancerNode')
GO

ALTER TABLE AnalysisTask ADD TimeQueued DATETIME2 NOT NULL
GO

CREATE NONCLUSTERED INDEX IX_AnalysisTask_Poll
ON AnalysisTask
(
    MeterID,
    NodeID DESC,
    Priority DESC,
    ID
)
GO

DROP TABLE FileGroupMeterConfiguration
GO

CREATE TABLE FileGroupAnalysisJobMeterConfiguration
(
    ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    FileGroupAnalysisJobID INT NOT NULL REFERENCES FileGroupAnalysisJob(ID),
    MeterConfigurationID INT NOT NULL REFERENCES MeterConfiguration(ID)
)
GO

CREATE NONCLUSTERED INDEX IX_FileGroupAnalysisJobMeterConfiguration_FileGroupAnalysisJobID
ON FileGroupAnalysisJobMeterConfiguration(FileGroupAnalysisJobID)
GO

CREATE NONCLUSTERED INDEX IX_FileGroupAnalysisJobMeterConfiguration_MeterConfigurationID
ON FileGroupAnalysisJobMeterConfiguration(MeterConfigurationID)
GO

ALTER TABLE FaultSummary
ADD PathEndSegmentID INT NOT NULL REFERENCES Asset(ID)
GO

ALTER TABLE DailyTrendingSummary
ADD LastReceived DATETIME NULL
GO

CREATE NONCLUSTERED INDEX IX_DailyTrendingSummary_ChannelID_Date
ON DailyTrendingSummary(ChannelID ASC, Date ASC)
GO

ALTER TABLE AlarmGroup
ADD Enabled BIT NOT NULL DEFAULT(1)
GO

DROP VIEW AlarmGroupView
GO

CREATE VIEW AlarmGroupView AS 
SELECT 
    AlarmGroup.ID,
    AlarmGroup.Name,
    AlarmSeverity.Name AlarmSeverity,
    CountStats.ChannelCount Channels,
    CountStats.MeterCount Meters,
    LastAlarm.StartTime LastAlarmStart,
    LastAlarm.EndTime LastAlarmEnd,
    LastAlarm.ChannelName LastChannel,
    LastAlarm.MeterName LastMeter,
    AlarmType.Description AS AlarmType,
    AlarmGroup.Enabled
FROM 
    AlarmGroup LEFT JOIN
    AlarmSeverity ON AlarmGroup.SeverityID = AlarmSeverity.ID LEFT JOIN
    AlarmType ON AlarmGroup.AlarmTypeID = AlarmType.ID OUTER APPLY
    (
        SELECT
            COUNT(DISTINCT Channel.ID) ChannelCount,
            COUNT(DISTINCT Channel.MeterID) MeterCount
        FROM
            Channel JOIN
            Series ON Series.ChannelID = Channel.ID JOIN
            Alarm ON Alarm.SeriesID = Series.ID
        WHERE Alarm.AlarmGroupID = AlarmGroup.ID
    ) CountStats OUTER APPLY
    (
        SELECT TOP 1
            LatestAlarmLog.StartTime,
            LatestAlarmLog.EndTime,
            Channel.Name ChannelName,
            Meter.Name MeterName
        FROM
            Alarm JOIN
            Series ON Alarm.SeriesID = Series.ID JOIN
            Channel ON Series.ChannelID = Channel.ID JOIN
            Meter ON Channel.MeterID = Meter.ID JOIN
            LatestAlarmLog ON LatestAlarmLog.AlarmID = Alarm.ID
        WHERE Alarm.AlarmGroupID = AlarmGroup.ID
        ORDER BY
            LatestAlarmLog.StartTime DESC,
            LatestAlarmLog.AlarmLogID DESC
    ) LastAlarm
GO

DROP VIEW ActiveAlarmView
GO

CREATE VIEW ActiveAlarmView AS
SELECT
    Alarm.ID AS AlarmID,
    Alarm.AlarmGroupID AS AlarmGroupID,
    AlarmGroup.AlarmTypeID AS AlarmTypeID,
    AlarmFactor.ID AS AlarmFactorID,
    AlarmFactor.SeverityID,
    Alarm.SeriesID AS SeriesID,
    AlarmFactor.Factor AS Value
FROM
    (
        SELECT
            AlarmFactor.ID,
            AlarmFactor.Factor,
            AlarmFactor.AlarmGroupID,
            AlarmFactor.SeverityID
        FROM
            AlarmFactor JOIN
            AlarmGroup ON AlarmFactor.AlarmGroupID = AlarmGroup.ID
        WHERE AlarmGroup.Enabled <> 0
        UNION
        SELECT
            NULL AS ID,
            1.0 AS Factor,
            AlarmGroup.ID AS AlarmGroupID,
            AlarmGroup.SeverityID
        FROM AlarmGroup
        WHERE AlarmGroup.Enabled <> 0
    ) AlarmFactor LEFT JOIN
    Alarm ON AlarmFactor.AlarmGroupID = alarm.AlarmGroupID LEFT JOIN
    AlarmGroup ON Alarm.AlarmGroupID = AlarmGroup.ID
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('FileWatcher.DeprioritizationThreshold', '0.0', '0.0')
GO

INSERT INTO Setting(Name, Value, DefaultValue) VALUES('FileWatcher.HighPriorityPaths', '', '')
GO

CREATE VIEW [OpenSEE.FaultSpecifics] AS
	SELECT
		Event.ID,
		FaultSummary.FaultType,
		FaultSummary.Inception,
		FaultSummary.DurationSeconds,
		FaultSummary.DurationCycles,
		FaultSummary.CurrentMagnitude,
		FaultSummary.Algorithm,
		FaultSummary.Distance,
		DoubleEndedFaultSummary.Distance as DoubleFaultDistance,
		DoubleEndedFaultSummary.Angle as DoubleFaultAngle,
		Event.StartTime,
		Meter.Name as MeterName
	FROM
		Event INNER JOIN
		Meter ON Meter.ID = Event.MeterID INNER JOIN
		FaultSummary ON FaultSummary.EventID = Event.ID AND FaultSummary.IsSelectedAlgorithm = 1 LEFT JOIN
		DoubleEndedFaultSummary ON DoubleEndedFaultSummary.EventID = Event.ID AND DoubleEndedFaultSummary.IsSelectedAlgorithm = 1
GO

DROP VIEW [SEBrowser.EventSearchEventView]
GO

CREATE VIEW [dbo].[SEBrowser.EventSearchEventView] AS
SELECT
    Event.ID AS EventID,
    FORMAT(Event.StartTime,'MM/dd/yyyy <br> HH:mm:ss.fffffff') AS Time,
    Meter.AssetKey AS [Meter Key],
    Meter.Name AS [Meter],
    Meter.Alias AS [Meter Alias],
    Meter.ShortName AS [Meter ShortName],
    Meter.Make AS [Meter Make],
    Meter.Model AS [Meter Model],
    Meter.TimeZone AS [Meter TimeZone],
    Meter.Description AS [Meter Desc],
    --Meter Sector
    --Meter Firmware Version
    --Meter Template Version
    --Meter Connection Type
    Location.Name AS [Station],
    Location.LocationKey AS [Station Key],
    Location.ShortName AS [Station ShortName],
    Location.Alias AS [Station Alias],
    Location.Description AS [Station Desc],
    --TSC
    Asset.AssetName AS [Asset Name],
    AssetType.Name AS [Asset Type],
    Asset.VoltageKV AS [Nom Voltage (kV)],
    Asset.Description AS [Asset Desc]
    --Asset Manufacturer
    --Asset Model
FROM
    Event JOIN
    Meter ON Event.MeterID = Meter.ID JOIN 
    Location ON Meter.LocationID = Location.ID JOIN
    Asset ON Event.AssetID = Asset.ID JOIN
    AssetType ON Asset.AssetTypeID = AssetType.ID
GO

DROP VIEW [EventWidgets.EventView]
GO

Create View [EventWidgets.EventView] AS
	SELECT
		Event.ID, 
		Event.StartTime,
		Event.EndTime,
		Event.AssetID,
		Meter.ID as MeterID, 
		Meter.Name as MeterName, 
		EventType.ID as EventTypeID,
		EventType.Name as EventType,
		distTbl.PerUnitMagnitude,
		distTbl.DurationSeconds,
		distTbl.Phase
	FROM
		Event JOIN
		Meter ON Meter.ID = Event.MeterID JOIN 
		EventType ON Event.EventTypeID = EventType.ID JOIN
		(
			Select Disturbance.DurationSeconds, Disturbance.PerUnitMagnitude, Phase.Name as Phase, Disturbance.EventID
			From Disturbance JOIN 
			EventWorstDisturbance ON EventWorstDisturbance.WorstDisturbanceID = Disturbance.ID JOIN
			Phase ON Phase.ID = Disturbance.PhaseID
			Where Disturbance.Magnitude in (Select Max(Disturbance.Magnitude) From Disturbance Group By Disturbance.EventID)
		) as distTbl ON distTbl.EventID = Event.ID
GO

CREATE VIEW [DetailedMeter] AS
	SELECT
		DISTINCT
		Meter.ID,
		Meter.AssetKey,
		Meter.Name,
		Meter.Make,
		Meter.Model,
		Location.Name as Location,
		COUNT(DISTINCT MeterAsset.AssetID) as MappedAssets
	FROM
		Meter LEFT JOIN
		Location ON Meter.LocationID = Location.ID LEFT JOIN
		MeterAsset ON Meter.ID = MeterAsset.MeterID LEFT JOIN
		Asset ON MeterAsset.AssetID = Asset.ID LEFT JOIN
		Note ON Note.NoteTypeID = (SELECT ID FROM NoteType WHERE Name = 'Meter') AND Note.ReferenceTableID = Meter.ID
	GROUP BY
		Meter.ID,
		Meter.AssetKey,
		Meter.Name,
		Meter.Make,
		Meter.Model,
		Location.Name
GO

CREATE VIEW [DetailedAsset] AS
	SELECT
		DISTINCT
		Asset.ID,
		Asset.AssetKey,
		Asset.AssetName,
		Asset.VoltageKV,
		AssetType.Name as AssetType,
		COUNT(DISTINCT Meter.ID) as Meters,
		COUNT(DISTINCT Location.ID) as Locations
	FROM
		Asset JOIN
		AssetType ON Asset.AssetTypeID = AssetType.ID LEFT JOIN
		MeterAsset ON MeterAsset.AssetID = Asset.ID LEFT JOIN
		Meter ON MeterAsset.MeterID = Meter.ID LEFT JOIN
		AssetLocation ON AssetLocation.AssetID = Asset.ID LEFT JOIN
		Location ON AssetLocation.LocationID = Location.ID
	GROUP BY
		Asset.ID,
		Asset.AssetKey,
		Asset.AssetName,
		Asset.VoltageKV,
		AssetType.Name
GO

CREATE VIEW [DetailedLocation] AS
	SELECT
		DISTINCT
		l.ID,
		l.LocationKey,
		l.Name,
		l.Alias,
		l.ShortName,
		l.Latitude,
		l.Longitude,
		l.Description,
		COUNT(DISTINCT m.ID) as Meters,
		COUNT(DISTINCT al.AssetID) as Assets
	FROM
		Location as l LEFT JOIN
		Meter as m ON l.ID = m.LocationID LEFT JOIN
		AssetLocation as al ON l.ID = al.LocationID LEFT JOIN
		Asset as a ON al.AssetID = a.ID
	GROUP BY
		l.ID,
		l.LocationKey,
		l.Name,
		l.Alias,
		l.ShortName,
		l.Latitude,
		l.Longitude,
		l.Description
GO

CREATE VIEW [PQDigest.ChannelView] AS
    SELECT DISTINCT 
        Channel.ID,
	    Channel.Name,
	    Channel.Description,
        Channel.Trend,
        Asset.ID as AssetID,
	    Asset.AssetKey,
	    Asset.AssetName,
        Meter.ID as MeterID,
	    Meter.AssetKey AS MeterKey,
	    Meter.Name AS MeterName,
        Meter.ShortName AS MeterShortName,
	    Phase.Name AS Phase,
        MeasurementType.Name AS MeasurementType,
        MeasurementCharacteristic.Name AS MeasurementCharacteristic,
	    ChannelGroup.Name AS ChannelGroup,
	    ChannelGroupType.DisplayName AS ChannelGroupType,
	    ChannelGroupType.Unit
    FROM 
	    Channel LEFT JOIN
	    Phase ON Channel.PhaseID = Phase.ID LEFT JOIN
	    Asset ON Asset.ID = Channel.AssetID LEFT JOIN
	    Meter ON Meter.ID = Channel.MeterID LEFT JOIN
        MeasurementType ON Channel.MeasurementTypeID = MeasurementType.ID LEFT JOIN
        MeasurementCharacteristic ON Channel.MeasurementCharacteristicID = MeasurementCharacteristic.ID LEFT JOIN
	    ChannelGroupType ON Channel.MeasurementCharacteristicID = ChannelGroupType.MeasurementCharacteristicID AND Channel.MeasurementTypeID = ChannelGroupType.MeasurementTypeID LEFT JOIN
	    ChannelGroup ON ChannelGroup.ID = ChannelGroupType.ChannelGroupID
GO